import pymysql
import uuid
import random
import json

mysql_conf = {
    "host": "localhost",
    "user": "root",
    "password": "123456",
    "db": "test1"
}

con_local = pymysql.connect(**mysql_conf)

cursor_local = con_local.cursor()


def sql_1_data_generator():
    CREATE_SQL = """
            CREATE TABLE my_order(
            id int primary key auto_increment,
            order_id varchar(255),
            store_name varchar(255),
            amount decimal(10, 2),
            create_time datetime default CURRENT_TIMESTAMP
            );
    """
    # cursor_local.execute(CREATE_SQL)
    SQL = """insert into my_order(order_id, store_name, amount) values (%s, %s, %s)"""
    # args = [(str(uuid.uuid1()), f"store_{random.randint(0, 16)}", format(random.random()*1000, ".2f"))
    args = [(str(uuid.uuid1()), f"store_{random.randint(0, 16)}", format(random.random()*1000, ".2f"))
            for i in range(0, 20)]
    cursor_local.executemany(SQL, args)
    con_local.commit()


def sql_2_data_generator():
    CREATE_SQL = """
                CREATE TABLE depart_store (
                id int primary key auto_increment,
                store_name varchar(255),
                department varchar(255)
                );
    """
    cursor_local.execute(CREATE_SQL)
    # 创建store_1~store_14的店铺列表
    store_array = [f"store_{i}" for i in range(15)]
    # 打乱store_array顺序
    random.shuffle(store_array)
    print(store_array)

    SQL = """insert into depart_store(department, store_name) values (%s, %s)"""
    for i in range(5):
        # 按间距选取3个store
        tmp_store_array = store_array[i::5]
        args = [(f"depart_{i}", f"{store_name}") for store_name in tmp_store_array]
        cursor_local.executemany(SQL, args)
    con_local.commit()



if __name__ == "__main__":
    sql_1_data_generator()
   # sql_2_data_generator()


# 1、现在有一批订单数据, 表结构如下, 请将订单数据汇总后算出店铺业绩.
# select store_name, sum(amount) as total from my_order GROUP BY store_name


# 2、现在有一张事业部和店铺的关系表, 请在订单表my_order的基础上汇总事业部的业绩.
# select t2.department,sum(total) as p_total from
# (select store_name, sum(amount) as total from my_order GROUP BY store_name) as t1
# join
# (select store_name, department from depart_store)as t2
# ON t1.store_name = t2.store_name
# GROUP BY t2.department


# 3、现在公司规定各个部门业绩都必须达到1600才算完成业绩目标, 我们需要统计一下达标率
# select sum(case when t.p_total > 1600 then 1 else 0 end)/count(*) as 达标率
# from
# (
# select t2.department,sum(amount) as p_total from
# (select store_name, amount from my_order) as t1
# left join
# (select store_name, department from depart_store)as t2
# ON t1.store_name = t2.store_name
# where t1.store_name is not NULL AND T2.store_name IS NOT NULL
# GROUP BY t2.department
# )t


# 4、现在事业部收到公司下达的达标指标, 然后又各自下达了店铺的达标指标, 需要每天统计订单数, 业绩总额, 达标率.


# 先把统计作用的底表做好
# select * from
# depart_store as t1
# join
# my_order as t2
# on
# t1.store_name = t2.store_name


# 如果店铺的达标阈值时700, 统计各个部门下的订单数, 总业绩, 店铺达标率
# select department,
# count(*)as order_count,
# sum(amount) as total_amount,
# sum(case when amount > 700 then 1 else 0 end)/count(*) as 达标率
# from
# (
# depart_store as t1
# join
# my_order as t2
# on
# t1.store_name = t2.store_name
# )
# GROUP BY department


# 统计各个店铺下单个订单金额超过600的总数和比率
# select t1.store_name,
# count(*)as order_count,
# sum(amount) as total_amount,
# sum(case when amount > 600 then 1 else 0 end)/count(*) as 达标率
# from
# (
# depart_store as t1
# join
# my_order as t2
# on
# t1.store_name = t2.store_name
# )
# GROUP BY t1.store_name


# 4.4如果店铺和部门是一对多的关系
# select t1.department,t1.store_name,
# count(*)as order_count,
# sum(amount) as total_amount,
# sum(case when amount > 600 then 1 else 0 end)/count(*) as 达标率
# from
# (
# depart_store as t1
# join
# my_order as t2
# on
# t1.store_name = t2.store_name
# )
# GROUP BY t1.store_name,t1.department
#
#
# 5、统计事业部的业绩数据, 并筛选出未达标的事业部数据
# 一般做法
# select * from
#  (
#  select department,
#  count(*)as order_count,
#  sum(amount) as total_amount,
#  sum(case when amount > 700 then 1 else 0 end)/count(*) as 达标率
#  from
#  (
#  depart_store as t1
#  join
#  my_order as t2
#  on
#  t1.store_name = t2.store_name
#  )
#  GROUP BY department
#  )t
#  where t.total_amount > 1600


# 更好的做法
#  select department,
#  count(*)as order_count,
#  sum(amount) as total_amount,
#  sum(case when amount > 700 then 1 else 0 end)/count(*) as 达标率
#  from
#  (
#  depart_store as t1
#  join
#  my_order as t2
#  on
#  t1.store_name = t2.store_name
#  )
#  GROUP BY department
#  HAVING sum(amount) > 1600